﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AppGauge.Data.SqlServer
{
    /// <summary>
    /// This class is the Low-Level DataBase Processor. This actually doesn't hold any sql queries, rather, the way it should execute the queries.
    /// </summary>
    public class DataContainer : IDisposable
    {

        #region [ Fields ]

        private SqlConnection con;
        private SqlDataAdapter da;
        private SqlCommand cmd;
        private DataTable dt;
        private string _connectionString;
        private string _SqlQuery;
        private string _table;
        private string _tKey;
        private string[] _columns;

        #endregion

        # region [ Properties ]

        /// <summary>
        /// Gets or sets the connection string.
        /// </summary>
        /// <value>
        /// The connection string.
        /// </value>
        /// <exception cref="System.Exception">Invalid ConnectionString. It should >10 and <500 Characters.</exception>
        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }
            set
            {
                if (value != null && value.Length > 10 && value.Length < 1000)
                {
                    this._connectionString = value;
                    Initialize();
                }
                else
                    throw new Exception("Invalid ConnectionString. It should >10 and <1000 Characters.");
            }
        }

        /// <summary>
        /// Gets or sets the SQL Query.
        /// </summary>
        /// <value>
        /// The SQL.
        /// </value>
        /// <exception cref="System.Exception">Null Query Provided</exception>
        public string SQL
        {
            get { return _SqlQuery; }
            set
            {
                if (value != null)
                    _SqlQuery = value;
                else
                    throw new Exception("Null Query Provided");
            }
        }

        /// <summary>
        /// Gets or sets the table.
        /// </summary>
        /// <value>
        /// The table.
        /// </value>
        /// <exception cref="System.Exception">Invalid Table Name</exception>
        public string Table
        {
            get { return _table; }
            set
            {
                if ((value != null) && (value.Length < 100))
                    _table = value;
                else
                    throw new Exception("Invalid Table Name");
            }
        }

        /// <summary>
        /// Gets or sets the T key.
        /// </summary>
        /// <value>
        /// The T key.
        /// </value>
        /// <exception cref="System.Exception">Invalid Primary Key</exception>
        public string TKey
        {
            get { return _tKey; }
            set
            {
                if ((value != null) && (value.Length < 100))
                    _tKey = value;
                else
                    throw new Exception("Invalid Primary Key");
            }
        }

        /// <summary>
        /// Gets or sets the columns.
        /// </summary>
        /// <value>
        /// The columns.
        /// </value>
        /// <exception cref="System.Exception">Invalid Columns Structure. Columns should be less than 50</exception>
        public string[] Columns
        {
            get { return _columns; }
            set
            {
                if ((value != null))
                {
                    _columns = new string[value.Length];
                    _columns = value;
                    _tKey = _columns[0].ToString();
                }

                else
                    throw new Exception("Invalid Columns Structure. Columns should be less than 50");
            }
        }
        # endregion
  
        # region [ Methods ]

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerInstance"/> class.
        /// </summary>
        /// <param name="CONNECTIONSTRING">The CONNECTIO n STRING.</param>
        public DataContainer(string CONNECTIONSTRING = null)
        {
            if (CONNECTIONSTRING != null)
            {
                ConnectionString = CONNECTIONSTRING;

            }
        }

        #region QueryBuilder

        /// <summary>
        /// Appends more columns to this Columns.
        /// </summary>
        /// <param name="MoreColumns">The more columns.</param>
        /// <returns>True</returns>
        public bool AppendColumns(string[] MoreColumns)
        {
            string[] tmp = new string[Columns.Length];
            tmp = Columns;
            Columns = new string[tmp.Length + MoreColumns.Length];
            tmp.CopyTo(Columns, 0);
            MoreColumns.CopyTo(Columns, tmp.Length - 1);

            return true;
        }

        /// <summary>
        /// Inserts the specified values as Arrays.
        /// </summary>
        /// <param name="Values">The values in Array.</param>
        /// <returns>Last inserted ID</returns>
        /// <exception cref="System.Exception">Insufficient Values Supplied. PLease check the length if the Values with the Columns</exception>
        /// <example>Insert(new string[]{"Pass","email","etc"});</example>
        public int Insert(string[] Values)
        {
            string[,] param = new string[Values.Length, 2];

            if (Values.Length == Columns.Length - 1)
            {
                int i;

                //insert intp table(column) output inserted.id values(@columns);
                SQL = "insert into " + Table + "(" + Columns[1];
                for (i = 2; i < Columns.Length; i++)
                    SQL += "," + Columns[i];

                SQL += ") output inserted."+TKey+" values(@" + Columns[1] + "";
                param[0, 0] = Columns[1];
                param[0, 1] = Values[0];
                for (i = 1; i < Values.Length; i++)
                {
                    SQL += ",@" + Columns[i+1] + "";
                    param[i, 0] = Columns[i + 1];
                    param[i, 1] = Values[i];
                }
                SQL += ")";


                return (int)Modify(SQL, param, true);
            }
            else
                throw new Exception("Insufficient Values Supplied. Please check the length if the Values with the Columns");
        }

        /// <summary>
        /// Updates the specified key.
        /// </summary>
        /// <param name="Key">The key.</param>
        /// <param name="Values">The values.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">Insufficient Values Supplied. PLease check the length if the Values with the Columns</exception>
        public bool Update(string[] Values, string Key = null)
        {
            //Dynamically Set Parameter Length based on no. of Values and If Key is required or not
            int paramLen = (Key == null) ? Values.Length : Values.Length + 1;
            string[,] param = new string[paramLen, 2];

            if (Values.Length == Columns.Length - 1)
            {
                int i;

                SQL = "update " + Table + " set " + Columns[1] + " =@" + Columns[1] + "";
                param[0, 0] = Columns[1];
                param[0, 1] = Values[0];
                for (i = 2; i < Columns.Length; i++)
                {
                    SQL += "," + Columns[i] + "= @" + Columns[i] + "";
                    param[i - 1, 0] = Columns[i];
                    param[i - 1, 1] = Values[i - 1];
                }
                if (Key != null)
                {
                    SQL += " where " + TKey + "=@" + TKey + "";
                    param[paramLen - 1, 0] = TKey;
                    param[paramLen - 1, 1] = Key;
                }
                return ((int)Modify(SQL, param) < 0) ? false : true;
            }
            else
                throw new Exception("Insufficient Values Supplied. Please check the length of the Values with the Columns");
        }

        /// <summary>
        /// Updates the Table with Selective Columns.
        /// </summary>
        /// <param name="Key">The key.</param>
        /// <param name="Fields">The fields.</param>
        /// <param name="Values">The values.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">Insufficient Values Supplied. PLease check the length of the Values with the Fields Provided</exception>
        public bool CustomUpdate(string[] Fields, string[] Values, string Key = null)
        {
            //Dynamically Set Parameter Length based on no. of Values and If Key is required or not
            int paramLen = (Key == null) ? Values.Length : Values.Length + 1;
            string[,] param = new string[paramLen, 2];

            if (Fields.Length == Values.Length)
            {
                int i;
                SQL = "update " + Table + " set " + Fields[0] + " =@" + Fields[0];
                param[0, 0] = Fields[0];
                param[0, 1] = Values[0];
                for (i = 1; i < Fields.Length; i++)
                {
                    SQL += "," + Fields[i] + "= @" + Fields[i];
                    param[i, 0] = Fields[i];
                    param[i, 1] = Values[i];
                }
                if (Key != null)
                {
                    SQL += " where " + TKey + "=@" + TKey + "";
                    param[paramLen - 1, 0] = TKey;
                    param[paramLen - 1, 1] = Key;
                }

                return ((int)Modify(SQL, param) < 0) ? false : true;
            }
            else
            {
                throw new Exception("Insufficient Values Supplied. PLease check the length of the Values with the Fields Provided");
            }
        }

        /// <summary>
        /// Deletes the specified key.
        /// </summary>
        /// <param name="Key">The key.</param>
        /// <returns></returns>
        public bool Delete(string Key)
        {
            string[,] param = new string[1, 2];
            SQL = "delete from " + Table + " where " + TKey + "=@" + TKey + "";
            param[0, 0] = TKey;
            param[0, 1] = Key;
            return ((int)Modify(SQL, param) < 0) ? false : true;
        }

        /// <summary>
        /// Views the specified key.
        /// </summary>
        /// <param name="Key">The key.</param>
        /// <returns></returns>
        public DataTable View(string Key = null)
        {
            string[,] param = new string[1, 2];
            SQL = "select * from " + Table;

            if (Key != null)
            {
                SQL += " where " + TKey + " =@" + TKey;
                param[0, 0] = TKey;
                param[0, 1] = Key;
            }

            return (DataTable)Fetch(SQL, param);
        }

        /// <summary>
        /// Customs the view.
        /// </summary>
        /// <param name="Key">The key.</param>
        /// <param name="CustomColumns">The custom columns.</param>
        /// <returns>DataTable that consists the result</returns>
        /// <example>CustomView(23,new string[]{"name"});</example>
        public DataTable CustomView(string[] CustomColumns = null, string Key = null)
        {
            string[,] param = new string[1, 2];

            SQL = "select ";

            if (CustomColumns != null)
            {
                SQL += CustomColumns[0];
                for (int i = 1; i < CustomColumns.Length; i++)
                {
                    SQL += "," + CustomColumns[i].ToString();
                }
            }
            else
                SQL += "*";


            SQL += " from " + Table;

            if (Key != null)
            {
                SQL += " where " + TKey + "=@" + TKey + "";
                param[1, 0] = TKey;
                param[1, 1] = Key;
            }

            return (DataTable)Fetch(SQL, param);
        }

        /// <summary>
        /// Searches the specified search item.
        /// </summary>
        /// <param name="SearchItem">The search item.</param>
        /// <param name="Fields">The fields.</param>
        /// <returns></returns>
        public DataTable Search(string SearchItem, string[] Fields)
        {
            string[,] param = new string[1, 2];

            SQL = "select * from " + Table + " where " + Fields[0] + " like @Search ";
            for (int i = 1; i < Fields.Length; i++)
            {
                SQL += " or " + Fields[i] + " like @Search";

            }
            param[0, 0] = "@Search";
            param[0, 1] = "%" + SearchItem + "%";

            return (DataTable)Fetch(SQL,param);
        }

        public DataTable Search(string[] SearchItem, string[] Fields)
        {
            string[,] param = new string[SearchItem.Length, 2];

            string SearchKey = "%" + SearchItem + "%";
            for (int i = 1; i < SearchItem.Length; i++)
                SearchKey += SearchItem[i] + "%";
            SQL = "select * from " + Table + " where " + Fields[0] + " like '" + SearchKey + "'";
            for (int i = 1; i < Fields.Length; i++)
                SQL += " or " + Fields[i] + " like '%" + SearchItem + "%'";

            return (DataTable)Fetch(SQL);
        }


        /// <summary>
        /// Checks if a specific value exists in a specific column
        /// </summary>
        /// <param name="Value">The value.</param>
        /// <param name="Field">The field.</param>
        /// <returns>True if found, else, false.</returns>
        public bool Exists(string Value, string Field)
        {
            string[,] param = new string[1, 2];

            SQL = "select " + Field + " from " + Table + " where " + Field + " =@Field";
            param[0, 0] = "Field";
            param[0, 1] = Value; ;

            DataTable dt = new DataTable();
            dt = (DataTable)Fetch(SQL, param);
            if (dt.Rows.Count<1)
                return false;
            else
                return true;

        }

        /// <summary>
        /// Matches the scalar value and returns the key as integer.
        /// </summary>
        /// <param name="SearchValues">The search value.</param>
        /// <param name="Field">The field.</param>
        /// <returns>Key Value as string where the 1st match has been found</returns>
        public string MatchScalar(string SearchValue, string Field)
        {
            SQL = "select * from " + Table + " where " + Field + "='" + SearchValue + "'";

            DataTable dt = new DataTable();
            dt = (DataTable)Fetch(SQL);
            if (dt.Rows.Count > 0)
                return dt.Rows[0][0].ToString();
            else
                return null;
        }

        /// <summary>
        /// Matches the values as array with corresponding fields.
        /// </summary>
        /// <param name="SearchValues">The search values.</param>
        /// <param name="Fields">The fields.</param>
        /// <returns>True if All the Values are Matched with those given in the field, else, false</returns>
        /// <example>
        /// <code>
        /// MatchValue(new string[]{"Snoopy","froppy"},new string[]{"secrestAns1","secrectAns2"});
        /// </code>
        /// This returns true if Snoopy and Froppy is in the respective fields in the table - SecretAns1 and SecretAns2
        /// </example>

        public bool MatchValue(string SearchValues, string Fields, string Key)
        {
            SQL = "select * from " + Table + " where " + Fields + "='" + SearchValues + "'";
            
            if(Key!=null)
                SQL += " where " + TKey + "='" + Key + "'";


            DataTable dt = new DataTable();
            dt = (DataTable)Fetch(SQL);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
       
        public bool MatchValue(string[] SearchValues, string[] Fields, string Key)
        {
            SQL = "select * from " + Table + " where " + Fields[0] + "='" + SearchValues[0] + "'";
            for (int i = 1; i < Fields.Length; i++)
                SQL += " and " + Fields[i] + "='" + SearchValues[i] + "'";

            if(Key!=null)
                SQL += " where " + TKey + "='" + Key + "'";


            DataTable dt = new DataTable();
            dt = (DataTable)Fetch(SQL);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// Execute a Custom query.
        /// </summary>
        /// <param name="SQLQuery">The SQL query.</param>
        /// <returns>Scalar Value</returns>
        public int CustomQuery(string SQLQuery)
        {
            int i;
            i = (int)Modify(SQLQuery);
            return i;
        }

        /// <summary>
        /// Execute a Custom query with DataTable.
        /// </summary>
        /// <param name="Query">The query.</param>
        /// <param name="DATATABLE">The DataTable as ref.</param>
        /// <example>
        /// DataTable dt= new DataTable();
        /// CustomQuery("select * from tbl",ref dt);
        /// </example>
        public void CustomQuery(string Query, ref DataTable DATATABLE)
        {
            string[,] param = new string[1, 2];
            DATATABLE.Clear();
            DATATABLE = (DataTable)Fetch(Query,param);
        }

        #endregion

        #region QueryProcessor
        /// <summary>
        /// Initializes this instance i.e. Creates a Connection Object.
        /// </summary>
        /// <remarks>No Need to call this explicitly</remarks>
        /// <exception cref="System.Exception">Database Connection Error</exception>
        private void Initialize()
        {
            try
            {
                con = new SqlConnection(@ConnectionString);
            }
            catch
            {
                throw new Exception("Malformed Connection String! ", new Exception("Connection String assumed as keywords! Try to change slashes."));

            }
            if (!TestConnection())
                throw new Exception("Database Connection Error");

        }

        /// <summary>
        /// Connects this instance to DB.
        /// </summary>
        /// <returns>True if Connected</returns>
        private bool Connect()
        {
            try
            {

                if (con.State == ConnectionState.Closed)
                    con.Open();
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Disconnects this instance.
        /// </summary>
        /// <returns>Returns true if success</returns>
        private bool Disconnect()
        {
            if (con.State == ConnectionState.Open)
                con.Close();

            return true;
        }

        /// <summary>
        /// Tests the connection to the DB.
        /// </summary>
        /// <returns>True if connection successful</returns>
        public bool TestConnection()
        {
            try
            {
                Connect();
            }
            catch
            {
                return false;
            }
            finally
            {
                Disconnect();
            }
            return true;
        }

        /// <summary>
        /// Modifies the specified SQL query. Use Insert, Update and Delete Commands.
        /// </summary>
        /// <param name="SqlQuery">The SQL query.</param>
        /// <param name="Parameters">Parameter Values</param>
        /// <param name="ReturnLastID">if set to <c>true</c> [return last ID].</param>
        /// <returns>Last Inserted ID if Auto-Increment is on.</returns>
        protected int Modify(string SqlQuery, string[,] Parameters = null, bool ReturnLastID = false)
        {
            SQL = SqlQuery;
            cmd = new SqlCommand(SQL, con);

            if (Connect())
            {
                for (int k = 0; k <= Parameters.GetUpperBound(0); k++)
                {
                    cmd.Parameters.AddWithValue(Parameters[k, 0], Parameters[k, 1]);
                }
                int lastid = 0;
                if (ReturnLastID == true)
                {
                   lastid =  (int)cmd.ExecuteScalar();  // Last Inserted ID
                   return lastid;
                }
                    
                else
                    cmd.ExecuteNonQuery();
                Disconnect();

                return 0; // Null Rows
            }
            else
                return -1; // Error
        }

        /// <summary>
        /// Fetches the specified SQL query.
        /// </summary>
        /// <param name="SqlQuery">The SQL query.</param>
        /// <returns>Result as DataTable</returns>
        protected DataTable Fetch(string SqlQuery, string[,] Parameters = null)
        {
            SQL = SqlQuery;
            cmd = new SqlCommand(SQL, con);
            if (Parameters[0,0] != null)
                for (int k = 0; k <= Parameters.GetUpperBound(0); k++)
                    cmd.Parameters.AddWithValue(Parameters[k, 0], Parameters[k, 1]);

            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count == 0)
                return null;

            return dt;
        }

        #endregion

        #region TableBuilder

        protected bool CreateTable(string TableName, string[, ,] TableColumns, string TKey)
        {
            return true;
        }

        /// <summary>
        /// Setups the model for a table.
        /// </summary>
        /// <param name="Values">The values(In Order) - TableName,PrimaryKeyColumnName,OtherColumnNames.</param>
        public void SetupModel(params string[] Values)
        {
            List<string> lst = new List<string>(Values);
            Table = lst[0];
            lst.RemoveAt(0);
            Columns = lst.ToArray();
        }
        #endregion

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            dt.Dispose();
            da.Dispose();
            cmd.Dispose();
            this.Dispose();
        }

        #endregion
    }
}
